#include "database.h"
#include <stdio.h>
#include <string.h>

static sqlite3* db = NULL;

// 通用查询回调函数
static int callback(void* data, int argc, char** argv, char** col_name) {
    for(int i = 0; i < argc; i++) {
        printf("%s = %s\n", col_name[i], argv[i] ? argv[i] : "NULL");
    }
    printf("--------------------\n");
    return 0;
}

bool db_init(const char* db_file) {
    int rc = sqlite3_open(db_file, &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        return false;
    }
    return true;
}

void db_close(void)
{
    if (db)
    {
        sqlite3_close(db);
        db = NULL;
    }
}

bool db_create_tables(void) {
    char* err_msg = NULL;
    const char* sql = "CREATE TABLE IF NOT EXISTS users ("
                     "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                     "name TEXT NOT NULL,"
                     "age INTEGER,"
                     "email TEXT);";

    int rc = sqlite3_exec(db, sql, NULL, NULL, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
        return false;
    }
    return true;
}

bool db_insert_user(const char* name, int age, const char* email) {
    sqlite3_stmt* stmt;
    const char* sql = "INSERT INTO users (name, age, email) VALUES (?, ?, ?);";
    
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return false;
    }

    sqlite3_bind_text(stmt, 1, name, -1, SQLITE_STATIC);
    sqlite3_bind_int(stmt, 2, age);
    sqlite3_bind_text(stmt, 3, email, -1, SQLITE_STATIC);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);

    return rc == SQLITE_DONE;
}

bool db_update_user(int id, const char* name, int age, const char* email) {
    sqlite3_stmt* stmt;
    const char* sql = "UPDATE users SET name = ?, age = ?, email = ? WHERE id = ?;";
    
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        return false;
    }

    sqlite3_bind_text(stmt, 1, name, -1, SQLITE_STATIC);
    sqlite3_bind_int(stmt, 2, age);
    sqlite3_bind_text(stmt, 3, email, -1, SQLITE_STATIC);
    sqlite3_bind_int(stmt, 4, id);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);

    return rc == SQLITE_DONE;
}

bool db_delete_user(int id) {
    sqlite3_stmt* stmt;
    const char* sql = "DELETE FROM users WHERE id = ?;";
    
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        return false;
    }

    sqlite3_bind_int(stmt, 1, id);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);

    return rc == SQLITE_DONE;
}

bool db_get_user(int id, User* user) {
    sqlite3_stmt* stmt;
    const char* sql = "SELECT * FROM users WHERE id = ?;";
    
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        return false;
    }

    sqlite3_bind_int(stmt, 1, id);

    if (sqlite3_step(stmt) == SQLITE_ROW) {
        user->id = sqlite3_column_int(stmt, 0);
        strncpy(user->name, (const char*)sqlite3_column_text(stmt, 1), sizeof(user->name) - 1);
        user->age = sqlite3_column_int(stmt, 2);
        strncpy(user->email, (const char*)sqlite3_column_text(stmt, 3), sizeof(user->email) - 1);
        sqlite3_finalize(stmt);
        return true;
    }

    sqlite3_finalize(stmt);
    return false;
}

bool db_list_users(void) {
    char* err_msg = NULL;
    const char* sql = "SELECT * FROM users;";

    int rc = sqlite3_exec(db, sql, callback, NULL, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
        return false;
    }
    printf("ID: Name\tAge\tEmail\n");
    return true;
}

bool db_execute_transaction(void) {
    char* err_msg = NULL;
    const char* sql = "BEGIN TRANSACTION;"
                     "INSERT INTO users (name, age, email) VALUES ('Alice', 25, 'alice@email.com');"
                     "INSERT INTO users (name, age, email) VALUES ('Bob', 30, 'bob@email.com');"
                     "COMMIT;";

    int rc = sqlite3_exec(db, sql, NULL, NULL, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Transaction failed: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
        return false;
    }
    return true;
}

bool db_search_users(const char* search_term) {
    sqlite3_stmt* stmt;
    const char* sql = "SELECT * FROM users WHERE name LIKE ? OR email LIKE ?;";
    
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        return false;
    }

    char search_pattern[103];
    snprintf(search_pattern, sizeof(search_pattern), "%%%s%%", search_term);
    
    sqlite3_bind_text(stmt, 1, search_pattern, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, search_pattern, -1, SQLITE_STATIC);

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        printf("ID: %d\n", sqlite3_column_int(stmt, 0));
        printf("Name: %s\n", sqlite3_column_text(stmt, 1));
        printf("Age: %d\n", sqlite3_column_int(stmt, 2));
        printf("Email: %s\n", sqlite3_column_text(stmt, 3));
        printf("--------------------\n");
    }

    sqlite3_finalize(stmt);
    return true;
}
